﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetLastNodeRevisions]'))
BEGIN
drop procedure [dbo].[medianamik_GetLastNodeRevisions]
END

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[medianamik_GetLastPublishedNodeRevisions]'))
BEGIN
EXEC('
CREATE PROCEDURE [dbo].[medianamik_GetLastPublishedNodeRevisions] 
	@NodeId UNIQUEIDENTIFIER , 
	@RevisionMax DATETIME
AS
BEGIN
	SET NOCOUNT ON;
	
	SELECT nr.* FROM medianamik_noderevision nr
	INNER JOIN (SELECT NodeId, Max(PublishedOn) as PublishedOn, ISNULL(CultureName, '''') as CultureName from medianamik_noderevision
	Where NodeId = @NodeId AND PublishedOn <= @RevisionMax AND PublishedOn is not null
	GROUP BY NodeId, CultureName) as nrjoin on nr.NodeId = nrjoin.NodeId AND ISNULL(nr.CultureName, '''') = nrjoin.CultureName and nr.PublishedOn = nrjoin.PublishedOn
	where nr.PublishedOn is not null
END')
END